package ct.zxd.db;

import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import ct.zxd.bean.Comment;
import ct.zxd.bean.Dish;
import ct.zxd.bean.User;

public class TCommentDao {
	
	/**
	 * 前端
	 * 当前userid用户 对dishid商品 评价 评分
	 * @param dishId
	 * @param userId
	 * @param content
	 * @param reply
	 * @param score
	 * @return
	 * @throws SQLException
	 */
	public int insertComment (Integer dishId, String userId, String content,  float score) throws SQLException {
		Connection conn=null;
		int result=0;	
		conn=DBconn.getconn();
		if(conn!=null){		
			String sql="insert into comment (dishid , userid , content , score , time) values(?,?,?,?,?)";
					PreparedStatement pstm=conn.prepareStatement(sql);
					pstm.setInt(1, dishId);
					pstm.setString(2, userId);
					pstm.setString(3, content);
					pstm.setFloat(4, score);
					pstm.setLong(5, new Date().getTime());
					result=pstm.executeUpdate();
					DBconn.closeconn(conn);
			}		
			return result;
		}
	
	

	/**
	 * 前端
	 *  用户评论指定商品后，对该商品进行评分累加，评论统计
	 * @param score
	 * @param dishId
	 * @return
	 * @throws SQLException
	 */
	public int updateDishComment(float score , Integer dishId) throws SQLException {
		Connection conn=null;
		int result=0;	
		conn=DBconn.getconn();
		if(conn!=null){	
			String sql = "update dish set good_comment = good_comment +  ?  ,comment_number = comment_number + 1 WHERE id =  + ? ";
			PreparedStatement pstm=conn.prepareStatement(sql);
			pstm.setFloat(1, score);
			pstm.setInt(2, dishId);
			result=pstm.executeUpdate();
			DBconn.closeconn(conn);
		}
		return result;
	}
	
	/**
	 * 后台
	 * 店家对用户指定的商品评价进行回复
	 * @param dishId
	 * @param reply
	 * @return
	 * @throws SQLException
	 */
	public int insertCommentReply(Integer id , String reply ) throws SQLException {
		Connection conn=null;
		int result=0;	
		conn=DBconn.getconn();
		if(conn!=null){	
			String sql  = " update comment set reply = ? where  id = ? " ;
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setString(1 , reply) ;
			pstm.setInt(2 , id);
			result = pstm.executeUpdate();
			DBconn.closeconn(conn);
		}
		return result;
	}
	
	/**
	 * 后台
	 * 更具指定的商品id,查询该商品的所有评论
	 * @param dishid
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("null")
	public List<Comment> selectCommentsByDishid(Integer dishid) throws SQLException{
		Connection conn = DBconn.getconn();
		List<Comment> list = new ArrayList<Comment>();
		if(conn!=null){	
			String sql = "select id , dishid , userid , content , reply , score, time from  comment where dishid = ? " ;
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setInt(1, dishid);
			ResultSet rs = pstm.executeQuery();
			  while(rs.next()) {
					Comment vo = new Comment() ;
					vo.setId(rs.getInt(1));
					vo.setDishId(rs.getInt(2));
				    vo.setUserId(rs.getString(3));
				    vo.setContent(rs.getString(4));
				    vo.setReply(rs.getString(5));
				    vo.setScore(rs.getFloat(6));
				    vo.setTime(rs.getLong(7));
				    vo.setUser(this.selectUserByUserid(rs.getString(3)));
				    list.add(vo);
			  }	 		 
		}
		 return list;
	}
	public User selectUserByUserid(String userId) {
		Connection conn = null ;
		PreparedStatement pstm = null ;
		User vo = null ;
		try {
			conn = DBconn.getconn();
		String sql = "select id,`name`,icon from user where id = ?" ;
		pstm = conn.prepareStatement(sql) ;
		pstm.setString(1, userId);
		ResultSet rs = pstm.executeQuery();
		while(rs.next()) {
			vo = new User();
			vo.setId(rs.getString(1));
//			vo.setName(rs.getString(2));
			vo.setName(URLDecoder.decode(rs.getString(2), "utf-8"));
			vo.setIcon(rs.getString(3));
		}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(!(conn == null)) {
				DBconn.closeconn(conn);
			}
		}
		return vo;
		}
	

	@SuppressWarnings("null")
	public List<Dish> selectDish() throws SQLException{
		Connection conn = DBconn.getconn();
		List<Dish> list = new ArrayList<Dish>() ;
		if(conn!=null){	
			String sql = "select id,`name` from  dish " ;
			PreparedStatement pstm = conn.prepareStatement(sql);
			ResultSet rs = pstm.executeQuery();		
			  while(rs.next()) {
				Dish vo = new Dish() ;
				vo.setDish_id(rs.getInt(1)) ;
				vo.setName(rs.getString(2)) ;
				list.add(vo) ;
			  }
		}
		return list;
	}
	
//	public List<Map<String,Object>> getDishList(String tableName , int displayPage,int pageSize,String condition) throws SQLException{
//        
//	  	Connection conn=DBconn.getconn();
//	  	
//		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
//		if(conn!=null){
//			String sql="select * from " + tableName + condition+" order by id  LIMIT ?,?";
//			
////			System.out.println(sql);
//			
//			PreparedStatement pstm=conn.prepareStatement(sql);
//			pstm.setInt(1, (displayPage-1)*pageSize);
//			pstm.setInt(2, pageSize);
//			ResultSet rs=pstm.executeQuery();
//			list=DBconn.getList(rs);
//			
////			System.out.println(list);
//			
//			DBconn.closeconn(conn);
//		}else{
//			 throw new SQLException("数据库连接错误");
//		}
//		return list;	
//  }
	
	
	
}
